from flask import Flask, request, jsonify, Response
from flask_cors import CORS
import pymysql
import csv
import io

app = Flask(__name__)
CORS(app)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'bank-system',
    'port': 3306,
    'cursorclass': pymysql.cursors.DictCursor,
    'autocommit': True
}

def get_conn():
    return pymysql.connect(**DB_CONFIG)

def to_csv(data):
    if not data:
        return ""
    output = io.StringIO()
    writer = csv.DictWriter(output, fieldnames=data[0].keys())
    writer.writeheader()
    writer.writerows(data)
    return output.getvalue()

# ========== 客户管理 ==========
@app.route('/api/customers', methods=['GET'])
def get_customers():
    q = request.args.get('q', '')
    page = int(request.args.get('page', 1))
    pageSize = int(request.args.get('pageSize', 10))
    offset = (page - 1) * pageSize
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (customerName LIKE %s OR PID LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        cur.execute(f"SELECT COUNT(*) as total FROM userInfo {where}", params)
        total = cur.fetchone()['total']
        cur.execute(f"SELECT * FROM userInfo {where} LIMIT %s, %s", params + [offset, pageSize])
        data = cur.fetchall()
    return jsonify({'data': data, 'total': total})

@app.route('/api/customers/export', methods=['GET'])
def export_customers():
    q = request.args.get('q', '')
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (customerName LIKE %s OR PID LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        cur.execute(f"SELECT * FROM userInfo {where}", params)
        data = cur.fetchall()
    csv_data = to_csv(data)
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=customers.csv"}
    )

@app.route('/api/customers/<int:id>', methods=['GET'])
def get_customer(id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (id,))
        data = cur.fetchone()
    return jsonify(data or {})

@app.route('/api/customers', methods=['POST'])
def add_customer():
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM userInfo WHERE PID=%s", (d['PID'],))
        if cur.fetchone():
            return jsonify({'error': '该身份证号已存在'}), 400
        cur.execute(
            "INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (%s, %s, %s, %s)",
            (d['customerName'], d['PID'], d.get('telephone'), d.get('address'))
        )
        cur.execute("SELECT * FROM userInfo WHERE customerID=LAST_INSERT_ID()")
        data = cur.fetchone()
    return jsonify(data), 201

@app.route('/api/customers/<int:id>', methods=['PUT'])
def update_customer(id):
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (id,))
        old = cur.fetchone()
        if not old:
            return jsonify({'error': '客户不存在'}), 404
        if d['PID'] != old['PID']:
            cur.execute("SELECT * FROM userInfo WHERE PID=%s", (d['PID'],))
            if cur.fetchone():
                return jsonify({'error': '该身份证号已被其他客户使用'}), 400
        cur.execute(
            "UPDATE userInfo SET customerName=%s, PID=%s, telephone=%s, address=%s WHERE customerID=%s",
            (d['customerName'], d['PID'], d.get('telephone'), d.get('address'), id)
        )
        cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (id,))
        data = cur.fetchone()
    return jsonify(data)

@app.route('/api/customers/<int:id>', methods=['DELETE'])
def delete_customer(id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (id,))
        if not cur.fetchone():
            return jsonify({'error': '客户不存在'}), 404
        cur.execute("DELETE FROM tradeInfo WHERE cardID IN (SELECT cardID FROM cardInfo WHERE customerID=%s)", (id,))
        cur.execute("DELETE FROM cardInfo WHERE customerID=%s", (id,))
        cur.execute("DELETE FROM userInfo WHERE customerID=%s", (id,))
    return jsonify({'success': True})

# ========== 银行卡管理 ==========
@app.route('/api/cards', methods=['GET'])
def get_cards():
    q = request.args.get('q', '')
    page = int(request.args.get('page', 1))
    pageSize = int(request.args.get('pageSize', 10))
    offset = (page - 1) * pageSize
    conn = get_conn()
    with conn.cursor() as cur:
        where = ""
        params = []
        if q:
            where = "WHERE (c.cardID LIKE %s OR u.customerName LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        count_sql = f"SELECT COUNT(*) as total FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID {where}"
        cur.execute(count_sql, params)
        total = cur.fetchone()['total']
        sql = f"SELECT c.*, u.customerName FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID {where} LIMIT %s, %s"
        cur.execute(sql, params + [offset, pageSize])
        data = cur.fetchall()
    return jsonify({'data': data, 'total': total})

@app.route('/api/cards/export', methods=['GET'])
def export_cards():
    q = request.args.get('q', '')
    conn = get_conn()
    with conn.cursor() as cur:
        where = ""
        params = []
        if q:
            where = "WHERE (c.cardID LIKE %s OR u.customerName LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        sql = f"SELECT c.*, u.customerName FROM cardInfo c JOIN userInfo u ON c.customerID = u.customerID {where}"
        cur.execute(sql, params)
        data = cur.fetchall()
    csv_data = to_csv(data)
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=cards.csv"}
    )

@app.route('/api/cards/<card_id>', methods=['GET'])
def get_card(card_id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT c.*, u.customerName, d.savingName 
            FROM cardInfo c
            JOIN userInfo u ON c.customerID = u.customerID
            JOIN deposit d ON c.savingID = d.savingID
            WHERE c.cardID = %s
            """, (card_id,))
        data = cur.fetchone()
    return jsonify(data or {})

@app.route('/api/cards', methods=['POST'])
def add_card():
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cardID = '10103576' + str(pymysql.rand())[2:10]
        cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (d['customerID'],))
        if not cur.fetchone():
            return jsonify({'error': '客户不存在'}), 400
        cur.execute("SELECT * FROM deposit WHERE savingID=%s", (d['savingID'],))
        if not cur.fetchone():
            return jsonify({'error': '存款类型不存在'}), 400
        cur.execute(
            "INSERT INTO cardInfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) VALUES (%s, 'RMB', %s, NOW(), %s, %s, %s, %s, %s)",
            (cardID, d['savingID'], d['openMoney'], d['openMoney'], d['pass'], d.get('IsReportLoss', '正常'), d['customerID'])
        )
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (cardID,))
        data = cur.fetchone()
    return jsonify(data), 201

@app.route('/api/cards/<card_id>', methods=['PUT'])
def update_card(card_id):
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (card_id,))
        old = cur.fetchone()
        if not old:
            return jsonify({'error': '银行卡不存在'}), 404
        if d['customerID'] != old['customerID']:
            cur.execute("SELECT * FROM userInfo WHERE customerID=%s", (d['customerID'],))
            if not cur.fetchone():
                return jsonify({'error': '客户不存在'}), 400
        if d['savingID'] != old['savingID']:
            cur.execute("SELECT * FROM deposit WHERE savingID=%s", (d['savingID'],))
            if not cur.fetchone():
                return jsonify({'error': '存款类型不存在'}), 400
        update_fields = ["customerID=%s", "savingID=%s", "openMoney=%s", "balance=%s", "IsReportLoss=%s"]
        params = [d['customerID'], d['savingID'], d['openMoney'], d['balance'], d['IsReportLoss']]
        if d.get('pass'):
            update_fields.append("pass=%s")
            params.append(d['pass'])
        params.append(card_id)
        cur.execute(f"UPDATE cardInfo SET {', '.join(update_fields)} WHERE cardID=%s", params)
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (card_id,))
        data = cur.fetchone()
    return jsonify(data)

@app.route('/api/cards/<card_id>', methods=['DELETE'])
def delete_card(card_id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (card_id,))
        if not cur.fetchone():
            return jsonify({'error': '银行卡不存在'}), 404
        cur.execute("DELETE FROM tradeInfo WHERE cardID=%s", (card_id,))
        cur.execute("DELETE FROM cardInfo WHERE cardID=%s", (card_id,))
    return jsonify({'success': True})

# ========== 存款业务管理 ==========
@app.route('/api/savings', methods=['GET'])
def get_savings():
    q = request.args.get('q', '')
    page = int(request.args.get('page', 1))
    pageSize = int(request.args.get('pageSize', 10))
    offset = (page - 1) * pageSize
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (savingName LIKE %s OR descrip LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        cur.execute(f"SELECT COUNT(*) as total FROM deposit {where}", params)
        total = cur.fetchone()['total']
        cur.execute(f"SELECT savingID, savingName, descrip AS description FROM deposit {where} LIMIT %s, %s", params + [offset, pageSize])
        data = cur.fetchall()
    return jsonify({'data': data, 'total': total})

@app.route('/api/savings/export', methods=['GET'])
def export_savings():
    q = request.args.get('q', '')
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (savingName LIKE %s OR descrip LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        cur.execute(f"SELECT savingID, savingName, descrip AS description FROM deposit {where}", params)
        data = cur.fetchall()
    csv_data = to_csv(data)
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=savings.csv"}
    )

@app.route('/api/savings/<saving_id>', methods=['GET'])
def get_saving(saving_id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID=%s", (saving_id,))
        data = cur.fetchone()
    return jsonify(data or {})

@app.route('/api/savings', methods=['POST'])
def add_saving():
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM deposit WHERE savingID=%s OR savingName=%s", (d['savingID'], d['savingName']))
        if cur.fetchone():
            return jsonify({'error': '业务ID或名称已存在'}), 400
        cur.execute(
            "INSERT INTO deposit (savingID, savingName, descrip) VALUES (%s, %s, %s)",
            (d['savingID'], d['savingName'], d.get('description'))
        )
        cur.execute("SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID=%s", (d['savingID'],))
        data = cur.fetchone()
    return jsonify(data), 201

@app.route('/api/savings/<saving_id>', methods=['PUT'])
def update_saving(saving_id):
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM deposit WHERE savingID=%s", (saving_id,))
        old = cur.fetchone()
        if not old:
            return jsonify({'error': '存款业务不存在'}), 404
        if d['savingName'] != old['savingName']:
            cur.execute("SELECT * FROM deposit WHERE savingName=%s AND savingID!=%s", (d['savingName'], saving_id))
            if cur.fetchone():
                return jsonify({'error': '该存款业务名称已被使用'}), 400
        cur.execute(
            "UPDATE deposit SET savingName=%s, descrip=%s WHERE savingID=%s",
            (d['savingName'], d.get('description'), saving_id)
        )
        cur.execute("SELECT savingID, savingName, descrip AS description FROM deposit WHERE savingID=%s", (saving_id,))
        data = cur.fetchone()
    return jsonify(data)

@app.route('/api/savings/<saving_id>', methods=['DELETE'])
def delete_saving(saving_id):
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM deposit WHERE savingID=%s", (saving_id,))
        if not cur.fetchone():
            return jsonify({'error': '存款业务不存在'}), 404
        cur.execute("SELECT * FROM cardInfo WHERE savingID=%s", (saving_id,))
        if cur.fetchone():
            return jsonify({'error': '该存款业务下存在关联的银行卡，无法删除'}), 400
        cur.execute("DELETE FROM deposit WHERE savingID=%s", (saving_id,))
    return jsonify({'success': True})

# ========== 交易记录管理 ==========
@app.route('/api/transactions', methods=['GET'])
def get_transactions():
    q = request.args.get('q', '')
    tradeDate = request.args.get('tradeDate', None)
    page = int(request.args.get('page', 1))
    pageSize = int(request.args.get('pageSize', 10))
    offset = (page - 1) * pageSize
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (cardID LIKE %s OR tradeType LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        if tradeDate:
            where += " AND DATE(tradeDate) = %s"
            params.append(tradeDate)
        cur.execute(f"SELECT COUNT(*) as total FROM tradeInfo {where}", params)
        total = cur.fetchone()['total']
        cur.execute(f"SELECT * FROM tradeInfo {where} ORDER BY tradeDate DESC LIMIT %s, %s", params + [offset, pageSize])
        data = cur.fetchall()
    return jsonify({'data': data, 'total': total})

@app.route('/api/transactions/export', methods=['GET'])
def export_transactions():
    q = request.args.get('q', '')
    tradeDate = request.args.get('tradeDate', None)
    conn = get_conn()
    with conn.cursor() as cur:
        where = "WHERE 1=1"
        params = []
        if q:
            where += " AND (cardID LIKE %s OR tradeType LIKE %s)"
            params += [f"%{q}%", f"%{q}%"]
        if tradeDate:
            where += " AND DATE(tradeDate) = %s"
            params.append(tradeDate)
        cur.execute(f"SELECT * FROM tradeInfo {where} ORDER BY tradeDate DESC", params)
        data = cur.fetchall()
    csv_data = to_csv(data)
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=transactions.csv"}
    )

# ========== 客户操作 ==========
@app.route('/api/customer/balance', methods=['POST'])
def customer_balance():
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT balance, IsReportLoss, pass FROM cardInfo WHERE cardID=%s", (d['cardID'],))
        row = cur.fetchone()
        if not row:
            return jsonify({'error': '银行卡不存在'}), 404
        if row['IsReportLoss'] == '挂失':
            return jsonify({'error': '该银行卡已挂失，无法操作'}), 403
        if row['pass'] != d['pass']:
            return jsonify({'error': '密码错误'}), 401
        return jsonify({'balance': row['balance']})

@app.route('/api/customer/transactions/<card_id>', methods=['GET'])
def customer_transactions(card_id):
    page = int(request.args.get('page', 1))
    pageSize = int(request.args.get('pageSize', 10))
    offset = (page - 1) * pageSize
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (card_id,))
        card = cur.fetchone()
        if not card:
            return jsonify({'error': '银行卡不存在'}), 404
        if card['IsReportLoss'] == '挂失':
            return jsonify({'error': '该银行卡已挂失，无法查询交易记录'}), 403
        cur.execute("SELECT * FROM tradeInfo WHERE cardID=%s ORDER BY tradeDate DESC LIMIT %s OFFSET %s", (card_id, pageSize, offset))
        rows = cur.fetchall()
        cur.execute("SELECT COUNT(*) as total FROM tradeInfo WHERE cardID=%s", (card_id,))
        total = cur.fetchone()['total']
    return jsonify({'data': rows, 'total': total, 'page': page, 'pageSize': pageSize})

@app.route('/api/customer/deposit', methods=['POST'])
def customer_deposit():
    d = request.json
    if d['amount'] <= 0:
        return jsonify({'error': '存款金额必须大于0'}), 400
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s AND pass=%s", (d['cardID'], d['pass']))
        card = cur.fetchone()
        if not card:
            return jsonify({'error': '银行卡号或密码错误'}), 400
        if card['IsReportLoss'] == '挂失':
            return jsonify({'error': '该银行卡已挂失，无法进行存款操作'}), 403
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    ('存款', d['cardID'], d['amount'], '存款操作'))
        cur.execute("UPDATE cardInfo SET balance = balance + %s WHERE cardID=%s", (d['amount'], d['cardID']))
    return jsonify({'success': True, 'message': '存款成功'})

@app.route('/api/customer/withdraw', methods=['POST'])
def customer_withdraw():
    d = request.json
    if d['amount'] <= 0:
        return jsonify({'error': '取款金额必须大于0'}), 400
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s AND pass=%s", (d['cardID'], d['pass']))
        card = cur.fetchone()
        if not card:
            return jsonify({'error': '银行卡号或密码错误'}), 400
        if card['IsReportLoss'] == '挂失':
            return jsonify({'error': '该银行卡已挂失，无法进行取款操作'}), 403
        if card['balance'] < d['amount']:
            return jsonify({'error': '余额不足，无法完成取款'}), 400
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    ('取款', d['cardID'], -d['amount'], '取款操作'))
        cur.execute("UPDATE cardInfo SET balance = balance - %s WHERE cardID=%s", (d['amount'], d['cardID']))
    return jsonify({'success': True, 'message': '取款成功'})

@app.route('/api/customer/report', methods=['POST'])
def customer_report():
    d = request.json
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s AND pass=%s", (d['cardID'], d['pass']))
        card = cur.fetchone()
        if not card:
            return jsonify({'error': '银行卡号或密码错误'}), 400
        if d['action'] == 'report':
            if card['IsReportLoss'] == '挂失':
                return jsonify({'error': '该银行卡已处于挂失状态'}), 400
            newStatus = '挂失'
            actionText = '挂失'
        elif d['action'] == 'cancel':
            if card['IsReportLoss'] == '正常':
                return jsonify({'error': '该银行卡未挂失，无需解挂'}), 400
            newStatus = '正常'
            actionText = '解挂'
        else:
            return jsonify({'error': '无效的操作类型'}), 400
        cur.execute("UPDATE cardInfo SET IsReportLoss=%s WHERE cardID=%s", (newStatus, d['cardID']))
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    (actionText, d['cardID'], 0, f"{actionText}操作"))
    return jsonify({'success': True, 'message': f'{actionText}成功'})

@app.route('/api/customer/changePassword', methods=['POST'])
def customer_change_password():
    d = request.json
    if d['oldPass'] == d['newPass']:
        return jsonify({'error': '新密码不能与旧密码相同'}), 400
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s AND pass=%s", (d['cardID'], d['oldPass']))
        card = cur.fetchone()
        if not card:
            return jsonify({'error': '银行卡号或旧密码错误'}), 400
        if card['IsReportLoss'] == '挂失':
            return jsonify({'error': '该银行卡已挂失，无法修改密码'}), 403
        cur.execute("UPDATE cardInfo SET pass=%s WHERE cardID=%s", (d['newPass'], d['cardID']))
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    ('修改密码', d['cardID'], 0, '修改密码操作'))
    return jsonify({'success': True, 'message': '密码修改成功'})

@app.route('/api/customer/transfer', methods=['POST'])
def customer_transfer():
    d = request.json
    if not d['fromCardID'] or not d['toCardID'] or not d['amount'] or not d['pass']:
        return jsonify({'error': '请填写所有字段'}), 400
    if d['fromCardID'] == d['toCardID']:
        return jsonify({'error': '不能给自己转账'}), 400
    if d['amount'] <= 0:
        return jsonify({'error': '转账金额必须大于0'}), 400
    conn = get_conn()
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s AND pass=%s", (d['fromCardID'], d['pass']))
        from_card = cur.fetchone()
        if not from_card:
            return jsonify({'error': '转出卡号或密码错误'}), 400
        if from_card['IsReportLoss'] == '挂失':
            return jsonify({'error': '转出卡已挂失，无法转账'}), 403
        if from_card['balance'] < d['amount']:
            return jsonify({'error': '余额不足'}), 400
        cur.execute("SELECT * FROM cardInfo WHERE cardID=%s", (d['toCardID'],))
        to_card = cur.fetchone()
        if not to_card:
            return jsonify({'error': '收款卡号不存在'}), 404
        if to_card['IsReportLoss'] == '挂失':
            return jsonify({'error': '收款卡已挂失，无法转账'}), 403
        cur.execute("UPDATE cardInfo SET balance = balance - %s WHERE cardID=%s", (d['amount'], d['fromCardID']))
        cur.execute("UPDATE cardInfo SET balance = balance + %s WHERE cardID=%s", (d['amount'], d['toCardID']))
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    ('转出', d['fromCardID'], -d['amount'], f'转账到{d["toCardID"]}'))
        cur.execute("INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (NOW(), %s, %s, %s, %s)",
                    ('转入', d['toCardID'], d['amount'], f'来自{d["fromCardID"]}'))
    return jsonify({'success': True, 'message': '转账成功'})

# ========== 报表统计与导出 ==========
@app.route('/api/reports/card-transactions', methods=['GET'])
def report_card_transactions():
    month = request.args.get('month', None)
    cardID = request.args.get('cardID', None)
    conn = get_conn()
    with conn.cursor() as cur:
        where = 'WHERE 1=1'
        params = []
        if month:
            where += ' AND DATE_FORMAT(tradeDate, "%Y-%m") = %s'
            params.append(month)
        if cardID:
            where += ' AND cardID = %s'
            params.append(cardID)
        cur.execute(f"SELECT cardID, COUNT(*) as tradeCount, SUM(tradeMoney) as totalAmount FROM tradeInfo {where} GROUP BY cardID", params)
        rows = cur.fetchall()
    return jsonify({'data': rows})

@app.route('/api/reports/month-summary', methods=['GET'])
def report_month_summary():
    month = request.args.get('month', None)
    conn = get_conn()
    with conn.cursor() as cur:
        where = ''
        params = []
        if month:
            where = 'WHERE DATE_FORMAT(tradeDate, "%Y-%m") = %s'
            params.append(month)
        cur.execute(f"""
            SELECT 
                COUNT(DISTINCT cardID) as activeCards,
                SUM(CASE WHEN tradeType='存款' THEN tradeMoney ELSE 0 END) as totalDeposit,
                SUM(CASE WHEN tradeType='取款' THEN -tradeMoney ELSE 0 END) as totalWithdraw,
                SUM(tradeMoney) as totalAmount
            FROM tradeInfo {where}
        """, params)
        row = cur.fetchone()
    return jsonify(row)

@app.route('/api/reports/export', methods=['GET'])
def export_report():
    type_ = request.args.get('type')
    month = request.args.get('month', None)
    conn = get_conn()
    with conn.cursor() as cur:
        rows = []
        if type_ == 'card-transactions':
            where = 'WHERE 1=1'
            params = []
            if month:
                where += ' AND DATE_FORMAT(tradeDate, "%Y-%m") = %s'
                params.append(month)
            cur.execute(f"SELECT cardID, COUNT(*) as tradeCount, SUM(tradeMoney) as totalAmount FROM tradeInfo {where} GROUP BY cardID", params)
            rows = cur.fetchall()
        elif type_ == 'month-summary':
            where = ''
            params = []
            if month:
                where = 'WHERE DATE_FORMAT(tradeDate, "%Y-%m") = %s'
                params.append(month)
            cur.execute(f"""
                SELECT 
                    COUNT(DISTINCT cardID) as activeCards,
                    SUM(CASE WHEN tradeType='存款' THEN tradeMoney ELSE 0 END) as totalDeposit,
                    SUM(CASE WHEN tradeType='取款' THEN -tradeMoney ELSE 0 END) as totalWithdraw,
                    SUM(tradeMoney) as totalAmount
                FROM tradeInfo {where}
            """, params)
            rows = cur.fetchall()
    csv_data = to_csv(rows)
    return Response(
        csv_data,
        mimetype="text/csv",
        headers={"Content-disposition": f"attachment; filename={type_}-{month or 'all'}.csv"}
    )

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=3000, debug=True)
